Rafał Gogłoza
Connect to Excel using PowerShell

PowerShell is a powerfull tool that will make your life easier. You can use it to automate your daily work or make boring taks interesting. It can also save you time to do whatherever you like.

If you are working on a Windows machine you already have it installed. This is an additional benefit.

As a bonus please find a git scripts at the end of the article. There are two working programs that are doing all the juicy stuff.

In this blog post you will see how can you:

  • Connect to Excel using PowerShell
  • Get a sheet name
  • Find a named table
  • Display value from the cell
  • Loop through table
  • Execute Excel Macro from PowerShell
  • And finally save an Excel file using PowerShell

1. How to connect to Excel using PowerShell

$excel = New-Object -ComObject Excel.Application 
$Excel.visible = $true
$workbook = $excel.Workbooks.Open($path)

2. How to get Excel sheet name using PowerShell:

Using an index of a sheet:

$structureDefinitionSheet = $workbook.Sheets.Item(1)

Using a sheet name:

$structureDefinitionSheet = $workbook.Sheets['Structure definition']

3. How to find named table in Excel using PowerShell

List objects and use where clause to find matching table name

$tableDefinitions = $structureDefinitionSheet.ListObjects | where-object { $_.DisplayName -eq "TableDefinition" }

From list objects, just specify the name

$tableDefinitions = $structureDefinitionSheet.ListObjects["TableDefinition"]

Additionally you might be interested in getting the list of all objects in the sheet:

$objects = $structureDefinitionSheet.ListObjects

4. How to display value from a cell using PowerShell

#this is not good practice
$configSheet.Range('B2').Value2
$configSheet.Range('C2').Value2

Maybe you can use tables? Or defined ranges. See next point for details.

5. How to loop through table

$rows = $tableDefinitions.ListRows
foreach($row in $rows) {
    $tableName = $row.Range.Columns[1].Value2
    $columnDefinition='  ' + $row.Range.Columns[2].Value2 + ' ' + $row.Range.Columns[3].Value2 + ' COMMENT "' + $row.Range.Columns[4].Value2 + '",'
}

6. How to execute an Excel Macro using PowerShell

$excel = New-Object -ComObject Excel.Application 
$Excel.visible = $true
$workbook = $excel.Workbooks.Open($path)

$macro = 'generateSelect'
$app = $excel.Application
$app.Run(($fileName+"!ThisWorkbook."+$macro)) #generateSelect

7. How to save Excel using PS

To the existing file:

$workbook.save()

To save a file with a different name:

$workbook.SaveAs($folder+"metaprogramming_version2.xlsx")

When you would like to use SaveAs option, but file already exists you will get a warning message. To avoid it you can use:

$Excel.DisplayAlerts = $false

This will hide the warning, override the file and close it.

How to find PowerShell useful

You may ask how PowerShell may be useful in developing DWH or BI solutions? The Simplest answers might be: to automate your work and make tedious task interesting.

For example, in Excel you may have a schema definition or master-data that you need to ingest into DWH. Then you may use PowerShell to automate your work and go through your data easier.

You may not only read data from Excel but, you may also write data to it. Imagine that you have a source definition in SQL Server and your Data Warehouse is on Oracle. It is fairly easy to read SQL Server definition into Excel and generate DWH Oracle code out of it. Or share the code with other team members.

You can also imagine that you have 100 Excels files that contains the same structure, and you would like to extract some information out of it. PowerShell can make your life easier.

Please understand me correctly. This is not the only way. But PowerShell might be useful, when connecting to Excel and knowing this possibility opens a lot of doors.

Please tell me how are you using Excel in your daily professional life. Do you see it handy to connect programmatically using PowerShell and automate your daily work?

Full code on GitHub

You can find full code on GitHub:
Connect to Excel using PowerShell
Run Excel Macro